package com.hnkywl.sts.dao;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.sfgl.Sfk;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import oracle.jdbc.OracleCallableStatement;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-5-14
 * Time: 上午11:26
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class BzjdrDao extends SimpleHibernateTemplate<Sfk, Long> {
    public Page<Sfk> findAllBzjdr(Integer sfklx, String zt, String bm, Date sfkrqStart, Date sfkrqEnd, String sbgsMc, Long sbgsId, String gldwMc, String cwsfpjlxMc, Integer cwsfZt, int pageNo, int pageSize) {
        String hql;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Sfk> page = new Page<Sfk>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (sbgsId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" sbgs.id = ? ");
            param.add(sbgsId);
        }
        if (sbgsId == null && sbgsMc != null && !sbgsMc.equals("")) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" sbgs.jc like ? ");
            param.add("%" + sbgsMc.trim() + "%");
        }
        if (zt != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" zt = ? ");
            param.add(zt);
        } else {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" zt = ? ");
            param.add("ytz");
        }
        if (bm != null && !bm.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" bm like ? ");
            param.add("%" + bm.trim() + "%");
        }
        if (sfkrqStart != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" sfkrq >= ?");
            param.add(sfkrqStart);
        }
        if (sfkrqEnd != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" sfkrq <= ?");
            param.add(sfkrqEnd);
        }
        if (gldwMc != null && !"".equals(gldwMc)) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" gldw.qc like ? ");
            param.add("%" + gldwMc.trim() + "%");
        }
        if (cwsfpjlxMc != null && !"".equals(cwsfpjlxMc)) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" pjlx.z like ? ");
            param.add("%" + cwsfpjlxMc.trim() + "%");
        }
        if (cwsfZt != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            //status=0未导入
            //status=1导入成功
            //status=2导入失败
            //status=3删除成功
            //status=4删除失败
            condition.append("( (" + cwsfZt + "=0 and insertFlag=1) " +
                    "or (" + cwsfZt + "=1 and insertFlag=3 and deleteFlag!=2) " +
                    "or (" + cwsfZt + "=2 and insertFlag=2) " +
                    "or (" + cwsfZt + "=3 and deleteFlag=3 and insertFlag!=2) " +
                    "or (" + cwsfZt + "=4 and deleteFlag=2) )");
        }
        //==============记录数量===============
      /*  126591L; // 代理费
        120591L; // 货款*/
        String sfk = " " + sfklx + "," + (sfklx + 1);
        hql = " select count(*) from Sfk where sfklx in (" + sfk + ") " +
                " and (sfBzj = 1 or kxsy_sjzdz_id not in (126591,120591))  " +
                RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        Long c;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Sfk where sfklx in (" + sfk + ")" +
                " and (sfBzj = 1 or kxsy_sjzdz_id not in (126591,120591)) " +
                RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        hql = hql + " order by dateCreated desc";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }


    /**
     * 保证金导入财务系统生成凭证
     *
     * @param cwsf
     * @return
     */
    public List<Map> getVoucher(Sfk cwsf) {
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call VOUCHER_BZJDR(?,?)}");
            call.setLong(1, cwsf.getId());
            call.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            //====================================
            rs = ((OracleCallableStatement) call).getCursor(2);
            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            List<Map> result = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                result.add(r);
            }
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }

    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }

}
